import sqlite3


class DBApi(object):
    SQLITE3_FILE = "sqlite.db"

    def __init__(self, db=None):
        if db is None:
            db = self.SQLITE3_FILE
        self.db = db
        self.conn = None
        self.cursor = None

        self.connect()

    def connect(self):
        self.conn = sqlite3.connect(self.db, isolation_level=None, uri=True)
        if self.conn is None:
            print("数据库连接错误")
        else:
            self.cursor = self.conn.cursor()

    def close(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()

    def exec_sql(self, sql):
        #print(f"exec_sql: {sql}")

        cursor = self.cursor.execute(sql)
        if cursor is None:
            print("数据库语句执行失败:{sql}")
        self.conn.commit()
        return cursor

    def exec_record_format(self, sql_fm, tuple_value):
        # cur.execute("INSERT INTO test values(?,?,?)", (6, "zgq", 20))
        # print(f"format: {format}, tuple_value: {tuple_value}")
        cur = self.cursor.execute(sql_fm, tuple_value)
        if cur is None:
            print("数据库语句执行失败:{sql}")
        else:
            self.conn.commit()
        return cur

    def exec_many_records_format(self, sql_fm, tuple_value_list):
        # cur.executemany('INSERT INTO test VALUES (?,?,?)', [(3, 'name3', 19), (4, 'name4', 26)])
        # print(f"format: {format}, tuple_value: {tuple_value_list}")

        print(sql_fm)
        print(tuple_value_list)
        cur = self.cursor.executemany(sql_fm, tuple_value_list)
        if cur is None:
            print("数据库语句执行失败:{sql}")
        else:
            self.conn.commit()
        return cur

    def exec_count(self, table_name, where: str=None):
        # SELECT COUNT(*) AS record_count
        # FROM your_table_name
        # WHERE id = 1111;
        if where:
            self.cursor.execute(f"SELECT COUNT(*) FROM {table_name} WHERE {where}")
        else:
            self.cursor.execute(f"SELECT COUNT(*) FROM {table_name}")
        obj_list = self.cursor.fetchone()
        if isinstance(obj_list, tuple) and len(obj_list) > 0:
            count = obj_list[0]
            # print(f"读取数量: {count}, {type(count)}")
        else:
            print(f"读取数量失败: {obj_list}")
            return None

        return count

    def exec_exist(self, sql):
        """
        判断是否存在某条记录
        """

        self.cursor.execute(sql)
        exists = self.cursor.fetchone()
        if isinstance(exists, tuple) and len(exists) > 0:
            exist = (exists[0] > 0)
            # print(f"{sql}---->{exist},{type(exist)}")
            return exist
        else:
            print(f"读取是否存在失败: {exists}, {sql}")
            return None
